﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PBOffice.Models;
using System.Data;
using System.Data.SqlClient;

namespace PBOffice.DAL
{
    public class StaffService
    {
        public List<Staff> GetAllStaff(string dep, string po, string name)
        {
            List<Staff> list = new List<Staff>();
            SqlParameter[] spa = {
                                     new SqlParameter("@Department",dep),
                                     new SqlParameter("@Position",po),
                                     new SqlParameter("@StfName",name)
                                 };

            using (IDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, "proc_DeptPosStfName", spa))
            {
                while (dr.Read())
                {
                    Staff staff = new Staff();
                    Department dept = new Department();
                    Position pos = new Position();
                    staff.StfId = (int)dr["StfId"];
                    staff.Password = dr["Password"].ToString();
                    staff.StfCode = dr["StfCode"].ToString();
                    staff.StfName = dr["StfName"].ToString();
                    staff.Gender = (bool)dr["Gender"];
                    dept.DeptName = dr["DeptName"].ToString();
                    staff.Department = dept;
                    pos.PosName = dr["PosName"].ToString();
                    staff.Position = pos;
                    staff.SuperId = (int)dr["SuperId"];
                    staff.Education = dr["Education"].ToString();
                    staff.WorkExp = dr["WorkExp"].ToString();
                    staff.BornDate = Convert.ToDateTime(dr["BornDate"]);
                    staff.JoinDate = Convert.ToDateTime(dr["JoinDate"]);
                    staff.Mail = dr["Mail"].ToString();
                    staff.TelePhone = dr["TelePhone"].ToString();
                    staff.TelePoneExt = dr["TelePhoneExt"].ToString();
                    staff.MobilePhone = dr["MobilePhone"].ToString();
                    staff.Zip = dr["Zip"].ToString();
                    staff.Address = dr["Address"].ToString();
                    staff.Status = (byte)dr["Status"];
                    list.Add(staff);
                }
            }
            return list;
        }
        public List<Staff> GetAllStaff(string dep, string po, string name, int pageIndex, int pageSize)
        {
            List<Staff> list = new List<Staff>();
            SqlParameter[] spa = {
                                     new SqlParameter("@Department",dep),
                                     new SqlParameter("@Position",po),
                                     new SqlParameter("@StfName",name),
                                     new SqlParameter("@PageIndex",pageIndex),
                                     new SqlParameter("@PageSize",pageSize)
                                 };

            using (IDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, "proc_DeptPosStfNamePage", spa))
            {
                while (dr.Read())
                {
                    Staff staff = new Staff();
                    Department dept = new Department();
                    Position pos = new Position();
                    staff.StfId = (int)dr["StfId"];
                    staff.Password = dr["Password"].ToString();
                    staff.StfCode = dr["StfCode"].ToString();
                    staff.StfName = dr["StfName"].ToString();
                    staff.Gender = (bool)dr["Gender"];
                    dept.DeptName = dr["DeptName"].ToString();
                    staff.Department = dept;
                    pos.PosName = dr["PosName"].ToString();
                    staff.Position = pos;
                    staff.SuperId = (int)dr["SuperId"];
                    staff.Education = dr["Education"].ToString();
                    staff.WorkExp = dr["WorkExp"].ToString();
                    staff.BornDate = Convert.ToDateTime(dr["BornDate"]);
                    staff.JoinDate = Convert.ToDateTime(dr["JoinDate"]);
                    staff.Mail = dr["Mail"].ToString();
                    staff.TelePhone = dr["TelePhone"].ToString();
                    staff.TelePoneExt = dr["TelePhoneExt"].ToString();
                    staff.MobilePhone = dr["MobilePhone"].ToString();
                    staff.Zip = dr["Zip"].ToString();
                    staff.Address = dr["Address"].ToString();
                    staff.Status = (byte)dr["Status"];
                    list.Add(staff);
                }
            }
            return list;
        }
        public List<Staff> GetNameByPosIdAndDeptId(int deptId, int posId)
        {
            List<Staff> list = new List<Staff>();
            string sql = string.Format("select * from Staff where PosId={0} and DeptId={1}", posId, deptId);
            using (IDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    Staff sa = new Staff();
                    sa.StfId = (int)dr["StfId"];
                    sa.StfName = dr["StfName"].ToString();
                    list.Add(sa);
                }
            }
            return list;
        }
        public Staff GetStaffByStfId(int stfId)
        {
            Staff staff = new Staff();
            SqlParameter[] spa ={new SqlParameter("@ID",stfId)
                                };

            using (IDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, "proc_GetStaffById", spa))
            {
                while (dr.Read())
                {
                    Department dept = new Department();
                    Position pos = new Position();
                    dept.DeptId = (int)dr["DeptId"];
                    dept.DeptName = dr["DeptName"].ToString();
                    pos.PosId = (int)dr["PosId"];
                    pos.PosName = dr["PosName"].ToString();
                    staff.Department = dept;
                    staff.Position = pos;
                    staff.StfId = (int)dr["StfId"];
                    staff.Password = dr["Password"].ToString();
                    staff.StfCode = dr["StfCode"].ToString();
                    staff.StfName = dr["StfName"].ToString();
                    staff.Gender = (bool)dr["Gender"];
                    staff.SuperId = (int)dr["SuperId"];
                    staff.Education = dr["Education"].ToString();
                    staff.WorkExp = dr["WorkExp"].ToString();
                    staff.BornDate = Convert.ToDateTime(dr["BornDate"]);
                    staff.JoinDate = Convert.ToDateTime(dr["JoinDate"]);
                    staff.Mail = dr["Mail"].ToString();
                    staff.TelePhone = dr["TelePhone"].ToString();
                    staff.TelePoneExt = dr["TelePhoneExt"].ToString();
                    staff.MobilePhone = dr["MobilePhone"].ToString();
                    staff.Zip = dr["Zip"].ToString();
                    staff.Address = dr["Address"].ToString();
                    staff.Status = (byte)dr["Status"];
                }
            }
            return staff;
        }
        public void AddStaff(Staff staff)
        {
            string sql = "insert [Staff] (Password,StfCode,StfName,Gender,DeptId,PosId,SuperId,Education,WorkExp,BornDate,JoinDate,Mail,"
           + "TelePhone,TelePhoneExt,MobilePhone,Zip,Address,Status) values(@Password,@StfCode,@StfName,@Gender,@DeptId,@PosId,@SuperId,@Education,"
           + "@WorkExp,@BornDate,@JoinDate,@Mail,@TelePhone,@TelePhoneExt,@MobilePhone,@Zip,@Address,@Status)";
            SqlParameter[] spa = {new SqlParameter("@Password",staff.Password),
                                     new SqlParameter("@StfCode",staff.StfCode),
                                     new SqlParameter("@StfName",staff.StfName),
                                     new SqlParameter("@Gender",staff.Gender),
                                     new SqlParameter("@DeptId",staff.Department.DeptId),
                                     new SqlParameter("@PosId",staff.Position.PosId),
                                     new SqlParameter("@SuperId",staff.SuperId),
                                     new SqlParameter("@Education",staff.Education),
                                     new SqlParameter("@WorkExp",staff.WorkExp),
                                     new SqlParameter("@BornDate",staff.BornDate),
                                     new SqlParameter("@JoinDate",staff.JoinDate),
                                     new SqlParameter("@Mail",staff.Mail),
                                     new SqlParameter("@TelePhone",staff.TelePhone),
                                     new SqlParameter("@TelePhoneExt",staff.TelePhone),
                                     new SqlParameter("@MobilePhone",staff.MobilePhone),
                                     new SqlParameter("@Zip",staff.Zip),
                                     new SqlParameter("@Address",staff.Address),
                                     new SqlParameter("@Status",staff.Status)
                                 };
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql, spa);
        }
        public void UpdateStaff(Staff staff)
        {
            string sql = "update [Staff] set Password=@Password,StfCode=@StfCode,StfName=@StfName,Gender=@Gender,DeptId=@DeptId,PosId=@PosId,"
            + "SuperId=@SuperId,Education=@Education,WorkExp=@WorkExp,BornDate=@BornDate,JoinDate=@JoinDate,Mail=@Mail,TelePhone=@TelePhone,"
          + "TelePhoneExt=@TelePhoneExt,MobilePhone=@MobilePhone,Zip=@Zip,Address=@Address,Status=@Status where StfId=@StfId";
            SqlParameter[] spa = {
                                     new SqlParameter("@StfId",staff.StfId),
                                     new SqlParameter("@Password",staff.Password),
                                     new SqlParameter("@StfCode",staff.StfCode),
                                     new SqlParameter("@StfName",staff.StfName),
                                     new SqlParameter("@Gender",staff.Gender),
                                     new SqlParameter("@DeptId",staff.Department.DeptId),
                                     new SqlParameter("@PosId",staff.Position.PosId),
                                     new SqlParameter("@SuperId",staff.SuperId),
                                     new SqlParameter("@Education",staff.Education),
                                     new SqlParameter("@WorkExp",staff.WorkExp),
                                     new SqlParameter("@BornDate",staff.BornDate),
                                     new SqlParameter("@JoinDate",staff.JoinDate),
                                     new SqlParameter("@Mail",staff.Mail),
                                     new SqlParameter("@TelePhone",staff.TelePhone),
                                     new SqlParameter("@TelePhoneExt",staff.TelePhone),
                                     new SqlParameter("@MobilePhone",staff.MobilePhone),
                                     new SqlParameter("@Zip",staff.Zip),
                                     new SqlParameter("@Address",staff.Address),
                                     new SqlParameter("@Status",staff.Status)
                                 };
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql, spa);
        }
        public string GetDeptNameByName(string name)
        {
            string sql = string.Format("select d.DeptName from Staff s,Department d where s.DeptId=d.DeptId and s.StfName='{0}'", name);
            string dept = null;
            using (SqlDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    dept = dr["DeptName"].ToString();
                }
            }
            return dept;
        }
        public List<Staff> GetAllStaffByDeptCodeName(string dep, string code, string name)
        {
            List<Staff> list = new List<Staff>();
            SqlParameter[] spa = {
                                     new SqlParameter("@Department",dep),
                                     new SqlParameter("@StfCode",code),
                                     new SqlParameter("@StfName",name)
                                 };

            using (IDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, "proc_DeptStfNoName", spa))
            {
                while (dr.Read())
                {
                    Staff staff = new Staff();
                    staff.StfId = (int)dr["StfId"];
                    staff.Password = dr["Password"].ToString();
                    staff.StfCode = dr["StfCode"].ToString();
                    staff.StfName = dr["StfName"].ToString();
                    staff.Gender = (bool)dr["Gender"];
                    staff.SuperId = (int)dr["SuperId"];
                    staff.Education = dr["Education"].ToString();
                    staff.WorkExp = dr["WorkExp"].ToString();
                    staff.BornDate = Convert.ToDateTime(dr["BornDate"]);
                    staff.JoinDate = Convert.ToDateTime(dr["JoinDate"]);
                    staff.Mail = dr["Mail"].ToString();
                    staff.TelePhone = dr["TelePhone"].ToString();
                    staff.TelePoneExt = dr["TelePhoneExt"].ToString();
                    staff.MobilePhone = dr["MobilePhone"].ToString();
                    staff.Zip = dr["Zip"].ToString();
                    staff.Address = dr["Address"].ToString();
                    staff.Status = (byte)dr["Status"];
                    list.Add(staff);
                }
            }
            return list;
        }
        public int GetCountByDeptPosName(string dept, string pos, string name)
        {
            SqlParameter[] spa = { 
                                     new SqlParameter("@Department",dept),
                                     new SqlParameter("@Position",pos),
                                     new SqlParameter("@StfName",name)
                                 };
            return Convert.ToInt32(SqlHelp.ExecuteScalar(SqlHelp.connectionstring, "proc_CountDeptPosName", spa));
        }
        public List<Staff> GetAllStaffByNameOrDept(string content)
        {
            List<Staff> list = new List<Staff>();
            SqlParameter[] spa = {
                                     new SqlParameter("@Content",content)
                                 };
            using (SqlDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, "proc_StfNameOrDeptName", spa))
            {
                while (dr.Read())
                {
                    Staff staff = new Staff();
                    Department dept = new Department();
                    dept.DeptName = dr["DeptName"].ToString();
                    dept.DeptId = (int)dr["DeptId"];
                    staff.Department = dept;
                    staff.StfName = dr["StfName"].ToString();
                    staff.StfId = (int)dr["StfId"];
                    list.Add(staff);
                }
            }
            return list;
        }

    }
}
